﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Zen4SyncPOCO.POCO.SQLServerExecution;
using System.Data.SqlClient;
using Zen4SyncPOCO.Enum.Admin;
using System.Data;
using System.ServiceModel;
using Zen4SyncOrchestratorServices;

namespace Zen4SyncClient.ActivityExecutors
{
    /// <summary>
    /// Provides methods to execute activities of the "SQL Server" type.
    /// </summary>
    internal static class SqlServer
    {
        internal static void ExecuteSqlServerExecution( SqlServerExecution activity, SqlServerExecutionContext context, Guid genericActivityId )
        {
            using ( SqlConnection sqlConnection = new SqlConnection( context.sqlServerExecutionContext_connectionString ) )
            {
                SqlCommand sqlCommand = new SqlCommand( activity.sqlServerExecution_sqlQuery, sqlConnection );
                foreach ( SqlServerExecutionParameter sqlServerExecutionParameter in context.sqlServerExecutionContext_parameters )
                {
                    object convertedValue = SqlValueParser.Parse(sqlServerExecutionParameter.sqlServerExecutionParameter_value, sqlServerExecutionParameter.sqlServerExecutionParameter_sqlValueTypeEnum);
                    SqlParameter sqlParameter = new SqlParameter(sqlServerExecutionParameter.sqlServerExecutionParameter_name, convertedValue);
                    sqlCommand.Parameters.Add(sqlParameter);
                }
                sqlConnection.Open();
                int affecte = sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                if ( Program.ClientSessionToExecute.clientSession_enableLog )
                {
                    Program.OrchestratorServices.Call( services => services.LogSqlServerExecution( Program.ClientSessionToExecute.clientSession_id, genericActivityId, context ) ); // TODO: WCF Orchestrator
                }
            }
        }

        /// <summary>
        /// Retrieves a value from the database using the SQL query of the activity and parses it into the type given by the activity.
        /// Parses the expected value into the type given by the activity and compare both parsed values.
        /// Exceptions are thrown if values can't be parsed or if parsed values are not equals.
        /// </summary>
        internal static void ExecuteSqlServerCheckData( SqlServerCheckData activity, SqlServerCheckDataContext context )
        {
            using ( SqlConnection sqlConnection = new SqlConnection( context.sqlServerCheckDataContext_connectionString ) )
            {
                SqlCommand sqlCommand = new SqlCommand( activity.sqlServerCheckData_sqlQuery, sqlConnection );
                foreach ( SqlServerCheckDataParameter sqlServerCheckDataParameter in context.sqlServerCheckDataContext_parameters )
                {
                    object convertedValue = SqlValueParser.Parse(sqlServerCheckDataParameter.sqlServerCheckDataParameter_value, sqlServerCheckDataParameter.sqlServerCheckDataParameter_sqlValueTypeEnum);
                    SqlParameter sqlParameter = new SqlParameter(sqlServerCheckDataParameter.sqlServerCheckDataParameter_name, convertedValue);
                    sqlCommand.Parameters.Add( sqlParameter );
                }

                object actualValue = null;
                byte[] expectedValue = activity.sqlServerCheckData_expectedValue;
                object parsedActualValue = null;
                object parsedExpectedValue = null;
                SqlValueTypeEnum valuesType = activity.sqlServerCheckData_sqlValueTypeEnum;

                sqlConnection.Open();

                actualValue = sqlCommand.ExecuteScalar();

                sqlConnection.Close();

                try
                {
                    parsedExpectedValue = SqlValueParser.Parse(expectedValue, valuesType);
                }
                catch(Exception exc)
                {
                    string exceptionMessage = UnableToParseExceptionMessage(valuesType, Encoding.UTF8.GetString(expectedValue), exc.Message);
                    throw new Exception(exceptionMessage);
                }

                try
                {
                    parsedActualValue = SqlValueParser.Parse(actualValue, valuesType);
                }
                catch (Exception exc)
                {
                    string exceptionMessage = UnableToParseExceptionMessage(valuesType, actualValue.ToString(), exc.Message);
                    throw new Exception(exceptionMessage);
                }

                if (!SqlValueComparer.ValuesAreEquals(valuesType, parsedExpectedValue, parsedActualValue))
                {
                    throw new Exception(CheckDataExceptionMessage(valuesType, parsedExpectedValue.ToString(), parsedActualValue.ToString()));
                }                
            }
        }

        /// <summary>
        /// Exception message indicating the impossibility to parse an object into a certain type.
        /// </summary>
        private static string UnableToParseExceptionMessage(SqlValueTypeEnum type, string objectToString, string parseExceptionMessage)
        {
            return String.Format("SqlServerCheckData : Unable to parse object into {0}.\nobject ToString() : {1}\nParse Exception Message : {2}", Enum.GetName(typeof(SqlValueTypeEnum), type), objectToString, parseExceptionMessage);
        }

        /// <summary>
        /// Exception message indicating the difference between two values supposed to be equals.
        /// </summary>
        private static string CheckDataExceptionMessage(SqlValueTypeEnum expectedType, string expectedValue, string actualValue)
        {
            return String.Format("SqlServerCheckData : Expected {0} {1} but was {2}", Enum.GetName(typeof(SqlValueTypeEnum), expectedType), expectedValue, actualValue);
        }
    }
}
